﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using CRMFramework;

namespace CRMBiz.Report
{
    public static class ReportHandler
    {
        /// <summary>
        /// Get Loan Purpose Report By Selected Date ( Year and Month)
        /// </summary>
        /// <param name="month"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public static DataTable GetLoanPurposeReportByDate(string month, string year)
        {
            DataTable loanPurposeReport = InitialDataTable();
            int personalAmount = GetDataByLoanPurpose(loanPurposeReport, month, year, "personal");
            int businessAmount = GetDataByLoanPurpose(loanPurposeReport, month, year, "business");
            int saccAmount = GetDataByLoanPurpose(loanPurposeReport, month, year, "sacc");
            int otherAmount = GetDataByLoanPurpose(loanPurposeReport, month, year, "other");
            int totalAmount = personalAmount + businessAmount + saccAmount + otherAmount;
            CalculatePercentageByLoanPurpose(loanPurposeReport, totalAmount);
            return loanPurposeReport;
        }


        /// <summary>
        /// Get Loan Purpose Report By Period (from startDate to finishDate)
        /// </summary>
        /// <param name="startDate"></param>
        /// <param name="finishDate"></param>
        /// <returns></returns>
        public static DataTable GetLoanPurposeReportByPeriod(DateTime startDate, DateTime finishDate)
        {
            DataTable loanPurposeReport = InitialDataTable();
            int personalAmount = GetDataByLoanPurpose(loanPurposeReport, startDate, finishDate, "personal");
            int businessAmount = GetDataByLoanPurpose(loanPurposeReport, startDate, finishDate, "business");
            int saccAmount = GetDataByLoanPurpose(loanPurposeReport, startDate, finishDate, "sacc");
            int otherAmount = GetDataByLoanPurpose(loanPurposeReport, startDate, finishDate, "other");
            int totalAmount = personalAmount + businessAmount + saccAmount + otherAmount;
            CalculatePercentageByLoanPurpose(loanPurposeReport, totalAmount);
            return loanPurposeReport;
        }


        /// <summary>
        /// Get state client Report By Selected Date ( Year and Month)
        /// </summary>
        /// <param name="month"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public static DataTable GetStateReportByDate(string month, string year)
        {
            DataTable statePurposeReport = InitialStateTable();
            int nswAmount = GetStateByClientDate(statePurposeReport, month, year, "NSW");
            int qldAmount = GetStateByClientDate(statePurposeReport, month, year, "QLD");
            int vicAmount = GetStateByClientDate(statePurposeReport, month, year, "VIC");
            int saAmount = GetStateByClientDate(statePurposeReport, month, year, "SA");
            int waAmount = GetStateByClientDate(statePurposeReport, month, year, "WA");
            int tasAmount = GetStateByClientDate(statePurposeReport, month, year, "TAS");
            int actAmount = GetStateByClientDate(statePurposeReport, month, year, "ACT");
            int ntAmount = GetStateByClientDate(statePurposeReport, month, year, "NT");

            int totalAmount = nswAmount + qldAmount + vicAmount + saAmount + waAmount + tasAmount + actAmount + ntAmount;
            CalculatePercentageByState(statePurposeReport, totalAmount);
            return statePurposeReport;
        }

        /// <summary>
        /// Get state client Report By Selected Date ( Year and Month)
        /// </summary>
        /// <param name="month"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public static DataTable GetStateReportByPeriod(String startDate, String finishDate)
        {
            DataTable statePurposeReport = InitialStateTable();
            int nswAmount = GetStateByClientPeriod(statePurposeReport, startDate, finishDate, "NSW");
            int qldAmount = GetStateByClientPeriod(statePurposeReport, startDate, finishDate, "QLD");
            int vicAmount = GetStateByClientPeriod(statePurposeReport, startDate, finishDate, "VIC");
            int saAmount = GetStateByClientPeriod(statePurposeReport, startDate, finishDate, "SA");
            int waAmount = GetStateByClientPeriod(statePurposeReport, startDate, finishDate, "WA");
            int tasAmount = GetStateByClientPeriod(statePurposeReport, startDate, finishDate, "TAS");
            int actAmount = GetStateByClientPeriod(statePurposeReport, startDate, finishDate, "ACT");
            int ntAmount = GetStateByClientPeriod(statePurposeReport, startDate, finishDate, "NT");

            int totalAmount = nswAmount + qldAmount + vicAmount + saAmount + waAmount + tasAmount + actAmount + ntAmount;
            CalculatePercentageByState(statePurposeReport, totalAmount);
            return statePurposeReport;
        }

        /// <summary>
        /// Initial Data table
        /// </summary>
        /// <returns></returns>
        private static DataTable InitialDataTable()
        {
            DataTable tb = new DataTable();
            tb.Columns.Add("PurposeSummary");
            tb.Columns.Add("TotalNumber");
            tb.Columns.Add("Percent");
            tb.Columns.Add("AverageLoanAmount");
            tb.Columns.Add("MedianLoanAmount");
            return tb;
        }

        //1st
        /// <summary>
        /// Initial State table
        /// </summary>
        /// <returns></returns>
        private static DataTable InitialStateTable()
        {
            DataTable tbl = new DataTable();
            tbl.Columns.Add("State");
            tbl.Columns.Add("Number");
            tbl.Columns.Add("Percent");
            return tbl;
        }


        /// <summary>
        /// Get Report data by Period
        /// </summary>
        /// <param name="tb"></param>
        /// <param name="fromDate"></param>
        /// <param name="toDate"></param>
        /// <param name="loanPurpose"></param>
        /// <returns></returns>
        private static int GetDataByLoanPurpose(DataTable tb, DateTime fromDate, DateTime toDate, string loanPurpose)
        {
            DataSet ds;
            int loanAmount = 0;
            try
            {
                string startDate = String.Format("{0:yyyy-MM-dd}", fromDate);
                string finishDate = String.Format("{0:yyyy-MM-dd}", toDate);
                string purposeType = loanPurpose;

                if (purposeType.ToUpper() != "OTHER")
                {
                    purposeType += "%";
                }
                var para = new[] 
                {
                    DBhelper.SetParameter("@p_startDate", startDate),
                    DBhelper.SetParameter("@p_finishDate",finishDate),
                    DBhelper.SetParameter("@p_PurposeSummary", purposeType)
                };

                ds = DBhelper.ExecuteDatasetSP("sp_GetLoanPurposeReportByPeriod", para);
                DataRow result = ds.Tables[0].Rows[0];
                loanAmount = InsertResultRowToTable(tb, result, loanPurpose);
                return loanAmount;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        /// <summary>
        /// Get Report Data by Specific Year and Month
        /// </summary>
        /// <param name="tb"></param>
        /// <param name="month"></param>
        /// <param name="year"></param>
        /// <param name="loanPurpose"></param>
        /// <returns></returns>
        private static int GetDataByLoanPurpose(DataTable tb, string month, string year, string loanPurpose)
        {
            DataSet ds;
            int loanAmount = 0;
            string purposeType = loanPurpose;
            try
            {
                if (purposeType.ToUpper() != "OTHER")
                {
                    purposeType += "%";
                }
                var para = new[] 
                {
                    DBhelper.SetParameter("@p_month",month),
                    DBhelper.SetParameter("@p_year",year),
                    DBhelper.SetParameter("@p_PurposeSummary",purposeType)
                };

                ds = DBhelper.ExecuteDatasetSP("sp_GetLoanPurposeReportByDate", para);
                DataRow result = ds.Tables[0].Rows[0];
                loanAmount = InsertResultRowToTable(tb, result, loanPurpose);
                return loanAmount;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Get State Data by Specific Year and Month
        /// </summary>
        /// <param name="tbl"></param>
        /// <param name="month"></param>
        /// <param name="year"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        private static int GetStateByClientDate(DataTable tbl, string month, string year, string state)
        {
            DataSet ds;
            int stateCount = 0;
            string stateType = state;
            try
            {
                if (stateType.ToUpper() != "NT")
                {
                    stateType += "%";
                }
                var para = new[] 
                {
                    DBhelper.SetParameter("@p_month",month),
                    DBhelper.SetParameter("@p_year",year),
                    DBhelper.SetParameter("@p_State",stateType)
                };

                ds = DBhelper.ExecuteDatasetSP("sp_GetClientStateReportByDate", para);
                DataRow result = ds.Tables[0].Rows[0];
                stateCount = InsertStateRowToTable(tbl, result, state);
                return stateCount;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Get State Data by Specific Selected Date ( Year and Month)
        /// </summary>
        /// <param name="tbl"></param>
        /// <param name="month"></param>
        /// <param name="year"></param>
        /// <param name="state"></param>
        /// <returns></returns>
        private static int GetStateByClientPeriod(DataTable tb, String fromDate, String toDate, string state)
        {
            DataSet ds;
            int stateCount = 0;
            string stateType = state;
            try
            {

                if (stateType.ToUpper() != "NT")
                {
                    stateType += "%";
                }
                var para = new[] 
                {
                    DBhelper.SetParameter("@p_startDate", fromDate),
                    DBhelper.SetParameter("@p_finishDate",toDate),
                    DBhelper.SetParameter("@p_State", stateType)
                };

                ds = DBhelper.ExecuteDatasetSP("sp_GetClientStateReportByPeriod", para);
                DataRow result = ds.Tables[0].Rows[0];
                stateCount = InsertStateRowToTable(tb, result, state);
                return stateCount;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// Read result data and insert to table row
        /// </summary>
        /// <param name="tb"></param>
        /// <param name="result"></param>
        /// <param name="loanPurpose"></param>
        /// <returns></returns>
        private static int InsertStateRowToTable(DataTable tbl, DataRow result, string state)
        {
            DataRow row = tbl.NewRow();
            row["State"] = state.ToUpper();
            row["Number"] = result["Number"].ToString();
            tbl.Rows.Add(row);
            return Convert.ToInt32(result["Number"].ToString());
        }



        /// <summary>
        /// Read result data and insert to table row
        /// </summary>
        /// <param name="tb"></param>
        /// <param name="result"></param>
        /// <param name="loanPurpose"></param>
        /// <returns></returns>
        private static int InsertResultRowToTable(DataTable tb, DataRow result, string loanPurpose)
        {
            DataRow row = tb.NewRow();
            row["PurposeSummary"] = loanPurpose.ToUpper();
            row["TotalNumber"] = result["TotalNumber"].ToString();
            row["AverageLoanAmount"] = result["AverageAmount"].ToString();
            row["MedianLoanAmount"] = result["MedianAmount"].ToString();
            tb.Rows.Add(row);
            return Convert.ToInt32(result["TotalNumber"].ToString());
        }
        /// <summary>
        /// Calculate percentage of each kind of purpose and insert last row to table
        /// </summary>
        /// <param name="tb"></param>
        /// <param name="totalAmount"></param>
        private static void CalculatePercentageByLoanPurpose(DataTable tb, int totalAmount)
        {
            foreach (DataRow row in tb.Rows)
            {
                int subtotal = Convert.ToInt32(row["TotalNumber"].ToString());
                double percentage = Math.Round((double)subtotal / totalAmount * 100, 2);
                row["Percent"] = percentage;
            }
            DataRow lastRow = tb.NewRow();
            lastRow["PurposeSummary"] = "<span style=\"font-weight:bold\">" + "TOTAL" + "</span>";
            lastRow["TotalNumber"] = "<span style=\"font-weight:bold\">" + totalAmount.ToString() + "</span>";
            lastRow["Percent"] = "<span style=\"font-weight:bold\">" + "100" + "</span>";
            tb.Rows.Add(lastRow);
        }


        /// <summary>
        /// Calculate percentage of each state and insert last row to table
        /// </summary>
        /// <param name="tb"></param>
        /// <param name="totalNumber"></param>
        private static void CalculatePercentageByState(DataTable tb, int totalNumber)
        {
            foreach (DataRow row in tb.Rows)
            {
                int subtotal = Convert.ToInt32(row["Number"].ToString());
                double percentage = Math.Round((double)subtotal / totalNumber * 100, 2);
                row["Percent"] = percentage;
            }
            DataRow lastRow = tb.NewRow();
            lastRow["State"] = "<span style=\"font-weight:bold\">" + "TOTAL" + "</span>";
            lastRow["Number"] = "<span style=\"font-weight:bold\">" + totalNumber.ToString() + "</span>";
            lastRow["Percent"] = "<span style=\"font-weight:bold\">" + "100" + "</span>";
            tb.Rows.Add(lastRow);
        }

        public static DataTable GetTest2014Report(string strState, string comesfrom)
        {
            DataTable dt = null;
            try
            {
                var para = new[] 
                {
                    DBhelper.SetParameter("@p_State", strState),
                    DBhelper.SetParameter("@p_ComesFrom",comesfrom ),
                };

                dt = DBhelper.ExecuteDatasetSP("sp_GetTest2014Report", para).Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return dt;
        }
    }
}
